package com.ruge.util.syncdata;

import com.alibaba.fastjson.JSONObject;
import lombok.extern.slf4j.Slf4j;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

@Slf4j
public class DbUtil {

    // 默认每页记录数
    public static final int DEFAULT_PAGE_SIZE = 5000;

    /**
     * 获取数据库连接
     *
     * @return 数据库连接
     * @throws SQLException SQL异常
     *
     */
    public static Connection getOracleSHOPDB03Conn() {
        String driverClass = "oracle.jdbc.OracleDriver";
        String url = "jdbc:oracle:thin:@192.168.0.99:1521/srvoyscm1";
        String username = "BUSHOPOWNER";
        String password = "Butech13991";
        Connection connection = null;
        try {
            // 加载驱动类
            Class.forName(driverClass);
            connection = DriverManager.getConnection(url, username, password);
        } catch (Exception e) {
            e.printStackTrace();
        }
        // 获取连接
        return connection;
    }

    public static Connection getOracleReportConn() {
        String driverClass = "oracle.jdbc.OracleDriver";
        String url = "jdbc:oracle:thin:@192.168.6.77:1521/srvrptoyscm1";
        String username = "BUSHOPOWNER";
        String password = "Butech13991";
        Connection connection = null;
        try {
            // 加载驱动类
            Class.forName(driverClass);
            connection = DriverManager.getConnection(url, username, password);
        } catch (Exception e) {
            e.printStackTrace();
        }
        // 获取连接
        return connection;
    }


    public static Connection getSapHanaConn() {
        String driverClass = "com.sap.db.jdbc.Driver";
        String url = "jdbc:sap://192.168.0.226:30015";
        String username = "OYJT";
        String password = "Sap12345";
        Connection connection = null;
        try {
            // 加载驱动类
            Class.forName(driverClass);
            connection = DriverManager.getConnection(url, username, password);
        } catch (Exception e) {
            e.printStackTrace();
        }
        // 获取连接
        return connection;
    }


    /**
     * 执行分页查询（自定义分页大小）
     *
     * @param connection 数据库连接
     * @param sql        原始SQL查询语句
     * @param pageNum    页码（从1开始）
     * @return 分页查询结果，List<JSONObject>
     */
    public static List<JSONObject> queryListSapHana(Connection connection, String sql, int pageNum) {
        // 参数校验
        if (connection == null || sql == null || sql.trim().isEmpty()) {
            throw new IllegalArgumentException("数据库连接和SQL语句不能为空");
        }
        if (pageNum < 1) {
            pageNum = 1; // 页码至少为1
        }
        int pageSize = DEFAULT_PAGE_SIZE;

        // 计算分页参数
        int offset = (pageNum - 1) * pageSize;

        // 构建SAP HANA分页SQL（使用LIMIT和OFFSET）
        String paginationSql = sql + " LIMIT ? OFFSET ?";

        List<JSONObject> resultList = new ArrayList<>();
        PreparedStatement pstmt = null;
        ResultSet rs = null;

        try {
            // 创建预处理语句
            pstmt = connection.prepareStatement(paginationSql);

            // 设置参数：先设置原始SQL的参数，再设置分页参数
            int paramIndex = 1;
            pstmt.setInt(paramIndex++, pageSize);  // LIMIT参数
            pstmt.setInt(paramIndex, offset);      // OFFSET参数

            // 执行查询
            rs = pstmt.executeQuery();

            // 获取结果集元数据（列信息）
            ResultSetMetaData metaData = rs.getMetaData();
            int columnCount = metaData.getColumnCount();

            // 处理结果集，转换为JSONObject
            while (rs.next()) {
                JSONObject jsonObject = new JSONObject();
                for (int i = 1; i <= columnCount; i++) {
                    // 获取列名和对应的值
                    String columnName = metaData.getColumnName(i);
                    Object columnValue = rs.getObject(i);

                    // 处理字符串类型的编码问题
                    if (columnValue instanceof String) {
                        jsonObject.put(columnName, columnValue);
                    } else {
                        jsonObject.put(columnName, columnValue);
                    }
                }
                resultList.add(jsonObject);
            }
        } catch (Exception e) {
            throw new RuntimeException(e);
        }

        return resultList;
    }


    /**
     * 计算符合条件的总记录数
     *
     * @param connection 数据库连接
     * @param sql        原始查询SQL
     * @return 总记录数
     */
    public static int getTotalCount(Connection connection, String sql) {
        // 参数校验
        if (connection == null || sql == null || sql.trim().isEmpty()) {
            throw new IllegalArgumentException("数据库连接和SQL语句不能为空");
        }

        try {
            PreparedStatement pstmt = connection.prepareStatement(sql);

            // 执行count查询
            ResultSet rs = pstmt.executeQuery();
            if (rs.next()) {
                return rs.getInt(1);
            }
            return 0;
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }


    public static int execUpdate(Connection connection, String sql) {
        int result = 0;
        try (PreparedStatement ps = connection.prepareStatement(sql)) {
            result = ps.executeUpdate();
        } catch (SQLException e) {
            log.error("sql:{} error:{}", sql, e.getMessage());
        }
        return result;
    }
}
